package sapling.dao;

import org.aspectj.runtime.internal.Conversions;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import sapling.model.Tree;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created by ever on 2016/10/9.
 */

@Transactional
public interface TreeDao extends JpaRepository<Tree, Long> {

    @Query(value = "SELECT * from tree where sales_state =?1",nativeQuery = true)
    List<Tree> findState( int state);

    @Query(value = "SELECT * FROM tree WHERE tree_well_id =?1 ORDER BY id DESC LIMIT 0,1",nativeQuery = true)
    Tree tree(Long tree_well_id);

    @Query(value = "SELECT * FROM tree WHERE tree_well_id =?1 and sales_state<>1 ORDER BY id DESC LIMIT 0,1",nativeQuery = true)
    Tree noouttree(Long tree_well_id);

    @Query(value = "SELECT * FROM tree WHERE tree_species like ?1 and tree_well_id is not null ",nativeQuery = true)
     List<Tree> trees(String tree_species);

    @Query(value = "SELECT * FROM tree WHERE tree_well_id in(" +
            "SELECT tree_well_id FROM tree_well WHERE land_mass_id =?1)",nativeQuery = true)
    List<Tree> trees(Long tree_well_id);

    @Query(value = "SELECT * FROM tree WHERE tree_species like ?1 and tree_well_id in?2 ",nativeQuery = true)
    List<Tree> trees(String tree_species,List<Long> tree_well_id);

    @Query(value = "SELECT * FROM tree WHERE clump =?1 and tree_well_id in?2 ",nativeQuery = true)
    List<Tree> treesClump(int clump,List<Long> tree_well_id);

    @Query(value = "SELECT * FROM tree WHERE response_state=?1 group by serial_number",nativeQuery = true)
    List<Tree> findResstate(Integer state);

    @Query(value = "SELECT * FROM tree WHERE serial_number=?1",nativeQuery = true)
    List<Tree> findSerialnumber(String serialnumber);

    @Query(value = "SELECT * FROM tree WHERE serial_number=?1 and tree_well_id<>0",nativeQuery = true)
    List<Tree> findSerialnumberandexists(String serialnumber);

    @Query(value = "SELECT * FROM tree WHERE serial_number=?1 and price=?2 and location is null ",nativeQuery = true)
    List<Tree> findSerialnumberAndPrice(String serialnumber,double price);

    @Query(value = "select serial_number from tree where price=0 and location is null and tree_well_id<>0 group by serial_number",nativeQuery = true)
    List<String> putserialList();

    @Query(value = "select * from tree where price=0 and location is null and tree_well_id<>0",nativeQuery = true)
    List<Tree> putserialTreeList();

    @Query(value = "select * from tree where response_state=0 and price>0",nativeQuery = true)
    List<Tree> findWillGenerBatch();

    @Query(value = "select * from tree where tree_species like ?1",nativeQuery = true)
    List<Tree> findbytreespec(String spec);

    @Query(value = "select * from tree where landmassid=?1",nativeQuery = true)
    List<Tree> findBylandmass(long landmassid);

    @Query(value = "select tree_species from tree where landmassid=?1 group by tree_species order by count(*) desc limit 0,1",nativeQuery = true)
    String getMaxTreeSpeclandmass(long landmassid);

    @Query(value = "SELECT * from tree GROUP BY serial_number order by createtime desc",nativeQuery = true)
    List<Tree> findPutWarehouse();

    @Query(value = "SELECT * from tree where put_nursery_time BETWEEN ?1 and ?2  GROUP BY serial_number order by createtime desc",nativeQuery = true)
    List<Tree> findPutSE(String strat,String end);

    @Query(value = "SELECT * FROM tree WHERE sales_state=?1",nativeQuery = true)
    List<Tree> findTeeSales(int state);

    @Query(value = "SELECT * FROM tree WHERE tree_species like ?1 and sales_state in (0,3,5) and landmassid =?2",nativeQuery = true)
    List<Tree> trees035(String tree_species,Long landmassid);

    @Query(value = "SELECT * from tree WHERE tree_species LIKE ?1 GROUP BY landmassid",nativeQuery = true)
    List<Tree> findTreeSpecies(String tree_species);

    @Query(value = "SELECT count(id) from tree WHERE tree_species LIKE ?1 AND landmassid =?2",nativeQuery = true)
    int findTreeNumber(String tree_species,Long landmassid);

    @Query(value = "SELECT count(id) from tree WHERE tree_species LIKE ?1 AND landmassid =?2 and sales_state =?3",nativeQuery = true)
    BigDecimal getNumberByLandMass(String treeSpecies,long landmassid,int state);

    @Query(value = "SELECT count(id) from tree WHERE landmassid =?1",nativeQuery = true)
    int findTreeNumber(Long landMass_id);

    @Query(value = "SELECT count(id) from tree WHERE landmassid =?1 and sales_state =?2",nativeQuery = true)
    BigDecimal getNumberByLandMass(Long landMass_id, int i);

    @Query(value = "SELECT * from tree WHERE landmassid =?1 GROUP BY tree_species",nativeQuery = true)
    List<Tree> findTreeSpeciesGroup(Long landmassid);

    @Query(value = "SELECT * from tree where landmassid in(SELECT id from land_mass) GROUP BY tree_species",nativeQuery = true)
    List<Tree> findTreeGroupNotSales();

    @Query(value = "SELECT * from tree where outbound_order_id =?1 and sales_state=?2",nativeQuery = true)
    List<Tree> findCompany(Long outbound_order_id,int sales_state);

    @Query(value = "SELECT * FROM tree WHERE response_state=?1",nativeQuery = true)
    List<Tree> findByResstate(Integer state);

    @Query(value = "SELECT * from tree where sales_state=?1 and batch_id is not null GROUP BY batch_id",nativeQuery = true)
    List<Tree> findByGROUPBybatch(int state);

    @Query(value = "SELECT count(id) from tree WHERE landmassid =?1 and sales_state <>1 and sales_state <>4",nativeQuery = true)
    int findTreeByNumber(Long landMass_id);

    @Query(value = "SELECT * from tree where landmassid in (SELECT id from land_mass) GROUP BY landmassid",nativeQuery = true)
    List<Tree> findCountLandmass();

    @Query(value = "SELECT * from tree WHERE sales_state=?1 GROUP BY mark_id",nativeQuery = true)
    List<Tree> listdie (int state);

    @Query(value = "SELECT * from tree WHERE sales_state=?1 GROUP BY outbound_order_id",nativeQuery = true)
    List<Tree> listout (int state);

    @Modifying
    @Query(value = "UPDATE tree set payment=?1 where serial_number=?2",nativeQuery = true)
    void updatePayment (Boolean payment, String serial_number);

    @Query(value = "SELECT * from tree GROUP BY tree_species",nativeQuery = true)
    List<Tree> findBytree_species();

    @Query(value = "SELECT * from tree GROUP BY height,crown_width,chest_breadth,clump,salenumber;" ,nativeQuery = true)
    List<Tree> findByReg();

    @Query(value = "SELECT * FROM tree WHERE tree_well_id =?1",nativeQuery = true)
    List<Tree> findtree(Long tree_well_id);

    @Query(value = "SELECT * from tree where landmassid=?1 and tree_species like?2 and clump=?3 GROUP BY height,crown_width,chest_breadth;",nativeQuery = true)
    List<Tree> findG3(Long landmassid,String spec,int clump);

    @Query(value = "SELECT * from tree where landmassid=?1 and tree_species like?2 and clump=?3 and height=?4 and crown_width =?5 and chest_breadth=?6",nativeQuery = true)
    List<Tree> findw6(Long landmassid,String spec,Boolean clump,double height,double crown_width ,double chest_breadth);

    @Query(value = "SELECT * FROM tree where batch_id =?1 and landmassid<>0",nativeQuery = true)
    List<Tree> findCount(Long batch_id);

    @Query(value = "SELECT * from tree where batch_id =?1 and sales_state=?2",nativeQuery = true)
    List<Tree> findBatchsatate(Long batch_id,int state);

    @Query(value = "SELECT  count(*) from tree where sales_state in(0,3,5) and landmassid <>0 ",nativeQuery = true)
    int findSum();
}


